﻿using Dapper;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class Content_DAL
    {
        public IEnumerable<Content> SearchContentList()
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                const string query = "select * from system_content_basic_content where IsValid=1 order by CreateTime desc";
                return SqlMapper.Query<Content>(conn, query);
            }
        }

        public IEnumerable<Content> SearchContentList(Int64 categoryId)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                const string query = "select * from system_content_basic_content where IsValid=1 and categoryId=@CategoryId order by CreateTime desc";
                return SqlMapper.Query<Content>(conn, query, new { @CategoryId = categoryId });
            }
        }

        public bool Add(Content content)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                //System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder(512);
                //sqlBuilder.Append("INSERT INTO System_Content_Basic_Content");

                //System.Text.StringBuilder sqlIntoBuilder = new System.Text.StringBuilder(216);
                //System.Text.StringBuilder sqlValueBuilder = new System.Text.StringBuilder(216);

                ////System.Collections.ObjectModel.Collection<System.Data.IDataParameter> sqlParameters = new System.Collections.ObjectModel.Collection<System.Data.IDataParameter>();

                //sqlIntoBuilder.Append("Contents");
                ////sqlValueBuilder.Append("@MIME");
                //sqlValueBuilder.Append("'" + content.Contents + "'");
                ////sqlParameters.Add(new SqlParameter("@MIME", resource.MIME));

                //if (content.CreateTime == null)
                //{
                //    throw new System.Exception("content.CreateTime不能为空");
                //}
                //sqlIntoBuilder.Append(", CreateTime");
                //sqlValueBuilder.Append(",'" + content.CreateTime + "'");
                ////sqlValueBuilder.Append(", @CreateTime");
                ////sqlParameters.Add(new SqlParameter("@CreateTime", resource.CreateTime));


                //if (content.LastUpdateTime == null)
                //{
                //    throw new System.Exception("content.LastUpdateTime不能为空");
                //}
                //sqlIntoBuilder.Append(", LastUpdateTime");
                //sqlValueBuilder.Append(",'" + content.LastUpdateTime + "'");

                //sqlBuilder.Append(" ( ");
                //sqlBuilder.Append(sqlIntoBuilder);
                //sqlBuilder.Append(" ) ");
                //sqlBuilder.Append(" VALUES ");
                //sqlBuilder.Append(" ( ");
                //sqlBuilder.Append(sqlValueBuilder);
                //sqlBuilder.Append(" ) ");
                //string sql = sqlBuilder.ToString();
                //CommandDefinition d = new CommandDefinition(sqlBuilder.ToString(), sqlParameters.ToArray());
                //return SqlMapper.Execute(conn, d) > 0;
                string insertSql = @"INSERT INTO System_Content_Basic_Content(Contents,CreateTime,LastUpdateTime,Title,CategoryId,Cover) values(@Contents,@CreateTime,@LastUpdateTime,@Title,@CategoryId,@Cover)";
                //return SqlMapper.Execute(conn, sqlBuilder.ToString()) > 0;
                return conn.Execute(insertSql, new { @Contents = content.Contents, @Title = content.Title, @CreateTime = content.CreateTime, @LastUpdateTime = content.LastUpdateTime, @CategoryId = content.CategoryId, @Cover =content.Cover}) > 0;
            }
        }

        public Int64 Add2(Content content)
        {
            bool result = Add(content);
            if (result)
            {
                Content model = GetByCreateTime(content.CreateTime);
                if (model != null)
                {
                    return model.ContentId;
                }
            }
            return 0;
        }

        public Content GetById(Int64 id)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string query = "select * from System_Content_Basic_Content where ContentId=" + id;
                return SqlMapper.Query<Content>(conn, query).FirstOrDefault();
            }
        }

        //public bool Update(Content content)
        //{
        //    using (IDbConnection conn = Hepler.OpenConnection())
        //    {
        //        string query = "select * from System_Content_Basic_Content where ContentId=" + id;
        //        return SqlMapper.Query<Content>(conn, query).FirstOrDefault();
        //    }
        //}

        public Content GetByCreateTime(DateTime createTime)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string query = "select * from System_Content_Basic_Content where CreateTime='" + createTime + "'";
                return SqlMapper.Query<Content>(conn, query).FirstOrDefault();
            }
        }

        public bool Update(string content, Int64 contentId, string title)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string updateSql = "update System_Content_Basic_Content set Title=@Title,Contents=@Contents,LastUpdateTime=@LastUpdateTime  where ContentId=@ContentId";
                return conn.Execute(updateSql, new { @Contents = content, @ContentId = contentId,@Title=title, @LastUpdateTime = DateTime.Now }) > 0;
            }
        }

        public bool Update(string content, Int64 contentId, string title,string cover)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string updateSql = "update System_Content_Basic_Content set Title=@Title,Contents=@Contents,LastUpdateTime=@LastUpdateTime,Cover=@Cover where ContentId=@ContentId";
                return conn.Execute(updateSql, new { @Contents = content, @ContentId = contentId, @Title = title, @LastUpdateTime = DateTime.Now, @Cover=cover }) > 0;
            }
        }

        public bool Delete(Int64 contentId)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string deleteSql = "update System_Content_Basic_Content set IsValid=0 where ContentId=@ContentId";
                return conn.Execute(deleteSql, new { @ContentId = contentId }) > 0;
            }
        }

        /// <summary>
        /// 根据时间获取最新的内容
        /// </summary>
        public Content GetContent(Int64 categoryId)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                const string query = "select * from system_content_basic_content where IsValid=1 and categoryId=@CategoryId order by CreateTime desc limit 1";
                return SqlMapper.Query<Content>(conn, query, new { @CategoryId = categoryId }).FirstOrDefault();
            }
        }

        //public string Save(Content content)
        //{
        //    Content model = GetById(content.ContentId);
        //    if (model != null)
        //    {

        //    }
        //}
    }
}
